package com.neusoft.elm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.elm.dao.BusinessDao;
import com.neusoft.elm.po.Admin;
import com.neusoft.elm.po.Business;
import com.neusoft.elm.util.DBUtil;

public class BusinessDaoImpl implements BusinessDao{
		private Connection con =null;
		private ResultSet  rs =null;
		private PreparedStatement pst =null;
	@Override
	public List<Business> getBusiness(String BusinessId,String BusinessName,String BusinessAddress) {
		List<Business> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer("select * from business where 1=1 ");
			 if(BusinessId!=null&&!BusinessId.equals("")) {
			 sql.append(" and BusinessId like '%"+BusinessId+"%' ");
			 }
			 if(BusinessName!=null&&!BusinessName.equals("")) {
			 sql.append(" and BusinessName like '%"+BusinessName+"%' ");
			 }
			 if(BusinessAddress!=null&&!BusinessAddress.equals("")) {
			 sql.append(" and BusinessAddress like '%"+BusinessAddress+"%' ");
			 }
		try {				 													 //导入查询到的商家信息
			con =DBUtil.getConnection();
			pst =con.prepareStatement(sql.toString());
			rs = pst.executeQuery();
			while(rs.next()) {
				Business business = new Business();
				business.setBusinessID(rs.getInt("BusinessId"));
				business.setPassword(rs.getString("Password"));
				business.setBusinessName(rs.getString("BusinessName"));
				business.setBusinessAddress(rs.getString("BusinessAddress"));
				business.setBusinessExplain(rs.getString("BusinessExplain"));
				business.setStartPrice(rs.getDouble("StartPrice"));
				business.setDeliveryPrice(rs.getDouble("DeliveryPrice"));
				list.add(business);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {               //释放资源
			DBUtil.close(rs, pst, con);
		}
		return list;
	}
	
	@Override
	public int postBusiness(String BusinessName) {
		int BusinessId = 0;
		String sql = "insert into Business(BusinessName,Password) value(?,'123')";
		try {				 										 //导入新建立的商家信息
			con =DBUtil.getConnection();
			//设置返回自增长列值
			pst =con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			pst.setString(1, BusinessName);
			pst.executeUpdate();
			//获取自增长值（1行1列）
			rs = pst.getGeneratedKeys();
			if(rs.next()) {
				BusinessId = rs.getInt(1);
			}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {               //释放资源
				DBUtil.close(rs, pst, con);
			}
		return BusinessId;
	}

	@Override
	public int deleteBusiness(int BusinessId) {
		int result = 0;
		String deleteFoodSql = "delete from food where BusinessId=?";
		String deleteBusinessSql = "delete from business where BusinessId=?";
		try {				 										 //删除所选商家
			con =DBUtil.getConnection();
			//开启事务
			con.setAutoCommit(false);
			
			pst = con.prepareStatement(deleteFoodSql);
			pst.setInt(1, BusinessId);
			pst.executeUpdate();
			
			pst = con.prepareStatement(deleteBusinessSql);
			pst.setInt(1, BusinessId);
			result = pst.executeUpdate();

			
			con.commit();
			//提交事务
			} catch (SQLException e) {
				result = 0;
				try {
					con.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				e.printStackTrace();
			} finally {               //释放资源
				DBUtil.close(null, pst, con);
			}
		
		return result;
	}

	@Override
	public Business getBusiness_Id_Password(Integer BusinessId, String Password) {
		Business business = null;
		String sql ="select * from business where BusinessId=? and Password = ? ";
		try {				 													 //导入查询到的商家信息
			con =DBUtil.getConnection();
			pst =con.prepareStatement(sql);
			pst.setInt(1, BusinessId);
			pst.setString(2, Password);
			rs = pst.executeQuery();
			while(rs.next()) {
				business = new Business();
				business.setBusinessID(rs.getInt("BusinessId"));
				business.setPassword(rs.getString("Password"));
				business.setBusinessName(rs.getString("BusinessName"));
				business.setBusinessAddress(rs.getString("BusinessAddress"));
				business.setBusinessExplain(rs.getString("BusinessExplain"));
				business.setStartPrice(rs.getDouble("StartPrice"));
				business.setDeliveryPrice(rs.getDouble("DeliveryPrice"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {               //释放资源
			DBUtil.close(rs, pst, con);
		}
		return business;
	}

	@Override
	public Business getBusiness_Id(Integer BusinessId) {
		Business business = null;
		String sql ="select * from business where BusinessId=?";
		try {				 													 //导入查询到的商家信息
			con =DBUtil.getConnection();
			pst =con.prepareStatement(sql);
			pst.setInt(1, BusinessId);
			rs = pst.executeQuery();
			while(rs.next()) {
				business = new Business();
				business.setBusinessID(rs.getInt("BusinessId"));
				business.setPassword(rs.getString("Password"));
				business.setBusinessName(rs.getString("BusinessName"));
				business.setBusinessAddress(rs.getString("BusinessAddress"));
				business.setBusinessExplain(rs.getString("BusinessExplain"));
				business.setStartPrice(rs.getDouble("StartPrice"));
				business.setDeliveryPrice(rs.getDouble("DeliveryPrice"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {               //释放资源
			DBUtil.close(rs, pst, con);
		}
		return business;
	}

	@Override
	public int putBusiness(Business business) {
		int result = 0;
		String sql = "update business set BusinessName=?,BusinessAddress=?,BusinessExplain=?,StartPrice=?,DeliveryPrice=? where BusinessID=?";
		try {				 										 //删除所选商家
			con =DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, business.getBusinessName());
			pst.setString(2, business.getBusinessAddress());
			pst.setString(3, business.getBusinessExplain());
			pst.setDouble(4, business.getStartPrice());
			pst.setDouble(5, business.getDeliveryPrice());
			pst.setInt(6, business.getBusinessID());
			result=pst.executeUpdate();

			} catch (SQLException e) {
				result = 0;
				try {
					con.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				e.printStackTrace();
			} finally {               //释放资源
				DBUtil.close(null, pst, con);
			}
		
		return result;		
	}

	@Override
	public int patchBusiness_Password(Integer BusinessId, String Password) {
		int result = 0;
		String sql = "update business set PassWord=? where BusinessID=?";
		try {				 										 //删除所选商家
			con =DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, Password);
			pst.setInt(2, BusinessId);
			result=pst.executeUpdate();
			} catch (SQLException e) {
				result = 0;
				try {
					con.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				e.printStackTrace();
			} finally {               //释放资源
				DBUtil.close(null, pst, con);
			}
		
		return result;		
	}
	

	
}
